package com.wms.service;

import com.mes.entity.User;
import com.yy.base.common.dao.Dao;
import com.yy.base.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Service
public class OutofstockService {
	@Autowired
	private Dao dao;

	//加载所有的设备备品配件领用信息
	public List<Map<String,Object>> getSparepartsList(String ok) {
		String sql  ="";

		sql +="SELECT " +
				"  ll.code as llcode, " +
				"  ll.m_warehouse_id as CKID, " +
				"  ck.NAME AS CKNAME, " +
				" (select name from m_warehouse where m_warehouse_id = ll.aims_warehouse_id) as MBNAME, " +
				" (select m_warehouse_id from m_warehouse where m_warehouse_id = ll.aims_warehouse_id) as MBID, " +
				"  ck.description AS TARGET, " +
				"  ll.wms_material_list_id as LLID, " +
				"   to_char(now(),'yyyy-mm-dd hh24:mm:ss') AS TIMES ," +
				"  wl.VALUE AS WLCODE, " +
				"  cz.name as CZNAME,  " +
				"  mx.use_status as USESTATUS ," +
				"  wl.m_product_id AS WLID,  " +
				"  wl.NAME AS wlname,  " +
				"  mx.qty AS MXQTY,  " +
				"  mx.qty_binding AS BDQTY,  " +
				"  (case when mx.binding_status = 'OK' then '已绑定' else '未绑定' end) AS BDSTATUS,  " +
				"  dw.NAME AS dwname,  " +
				"  dw.c_uom_id AS DWID,  " +
				"  ys.NAME AS ysname,  " +
				"  ys.emes_color_id AS YSID,  " +
				"  cm.code AS cmcode,  " +
				"  cm.emes_size_id AS CMID,  " +
				"  gys.NAME AS gysname,  " +
				"  gys.emes_vendor_id AS GYSID  ," +
				"  mx.Wms_Material_List_Mx_id as LISTID  ," +
				"  wlfl.value as WLTYPE  " +
				"FROM  " +
				"  Wms_Material_List_Mx mx  " +
				"  LEFT JOIN wms_material_list ll on ll.wms_material_list_id = mx.wms_material_list_id  " +
				"  LEFT JOIN m_product wl ON mx.m_product_id = wl.m_product_id  " +
				"  LEFT JOIN m_product_category wlfl on wl.m_product_category_id = wlfl.m_product_category_id " +
				"  left join Emes_Material cz on cz.Emes_Material_ID = wl.Emes_Material_ID " +
				"  LEFT JOIN c_uom dw ON mx.c_uom_id = dw.c_uom_id  " +
				"  LEFT JOIN emes_color ys ON mx.emes_color_id = ys.emes_color_id  " +
				"  LEFT JOIN emes_size cm ON mx.emes_size_id = cm.emes_size_id  " +
				"  LEFT JOIN emes_vendor gys ON mx.emes_vendor_id = gys.emes_vendor_id " +
				"  LEFT JOIN m_warehouse ck ON ll.m_warehouse_id = ck.m_warehouse_id  " +
				" where mx.use_status = 'CREATE' and wlfl.value != 'SC-XT' " ;
		/*if(ok.equals("YES") ){
			sql +=" and wlfl.value = 'SC_DD' ";
		}else if(ok.equals("NO") ){
			sql +=" and wlfl.value != 'SC_DD' ";
		}*/


		return dao.query(sql);
	}


	//加载所有的设备备品配件领用信息
	public List<Map<String,Object>> getSparepartsLists(String llcode,String COLORID ,String SIZEID,String ok, String wlname) {
		String sql  ="";
		sql += "SELECT " +
				"  ll.code as llcode, " +
				"  ll.m_warehouse_id as CKID, " +
				"  ck.NAME AS CKNAME, " +
				" (select name from m_warehouse where m_warehouse_id = ll.aims_warehouse_id) as MBNAME, " +
				" (select m_warehouse_id from m_warehouse where m_warehouse_id = ll.aims_warehouse_id) as MBID, " +
				"  ck.description AS REMAKES, " +
				"  ll.wms_material_list_id as LLID, " +
				"   to_char(now(),'yyyy-mm-dd hh24:mm:ss') AS TIMES ," +
				"  wl.VALUE AS WLCODE,  " +
				"  cz.name as CZNAME,  " +
				"  mx.use_status as USESTATUS ," +
				"  wl.m_product_id AS WLID,  " +
				"  wl.NAME AS wlname,  " +
				"  mx.qty AS MXQTY,  " +
				"  mx.qty_binding AS BDQTY,  " +
				"  (case when mx.binding_status = 'OK' then '已绑定' else '未绑定' end) AS BDSTATUS,  " +
				"  dw.NAME AS dwname,  " +
				"  dw.c_uom_id AS DWID,  " +
				"  ys.NAME AS ysname,  " +
				"  ys.emes_color_id AS YSID,  " +
				"  cm.code AS cmcode,  " +
				"  cm.emes_size_id AS CMID,  " +
				"  gys.NAME AS gysname,  " +
				"  gys.emes_vendor_id AS GYSID   ," +
				"  mx.Wms_Material_List_Mx_id as LISTID " +
				"FROM  " +
				"  Wms_Material_List_Mx mx  " +
				"  LEFT JOIN wms_material_list ll on ll.wms_material_list_id = mx.wms_material_list_id  " +
				"  LEFT JOIN m_product wl ON mx.m_product_id = wl.m_product_id  " +
				"  LEFT JOIN m_product_category wlfl on wl.m_product_category_id = wlfl.m_product_category_id " +
				"  left join Emes_Material cz on cz.Emes_Material_ID = wl.Emes_Material_ID " +
				"  LEFT JOIN c_uom dw ON mx.c_uom_id = dw.c_uom_id  " +
				"  LEFT JOIN emes_color ys ON mx.emes_color_id = ys.emes_color_id  " +
				"  LEFT JOIN emes_size cm ON mx.emes_size_id = cm.emes_size_id  " +
				"  LEFT JOIN emes_vendor gys ON mx.emes_vendor_id = gys.emes_vendor_id " +
				"  LEFT JOIN m_warehouse ck ON ll.m_warehouse_id = ck.m_warehouse_id  " +
				"  where mx.use_status = 'CREATE' and wlfl.value != 'SC-XT' " ;
		/*if(ok.equals("YES") ){
			sql +=" and wlfl.value = 'SC_DD' ";
		}else if(ok.equals("NO") ){
			sql +=" and wlfl.value != 'SC_DD' ";
		}*/

		if(llcode != null && !llcode.equals("") && !llcode.equals("null") ){
			sql +="  and ll.code like '%"+llcode+"%' ";
		}
		if(wlname != null && !wlname.equals("") && !wlname.equals("null") ){
			sql +="  and wl.name like '%"+wlname+"%' ";
		}
		if(COLORID != null && !COLORID.equals("") && !COLORID.equals("null")){
			sql +="  and ys.NAME like '%"+COLORID+"%' ";
		}
		if(SIZEID != null && !SIZEID.equals("") && !SIZEID.equals("null")){
			sql +="  and cm.code like '%"+SIZEID+"%'";
		}

		return dao.query(sql);
	}
	//加载设备备品配件物料的库存数量
	public List<Map<String,Object>> getSumMateriels(String wlID,String sizeID ,String colorID) {
		String sql  ="";
		sql += " select sum(mx.qty) as QTYS ,bc.wms_storage_batch_id as BATCHID " +
				" from wms_storage_in_out mx, wms_storage_batch bc " +
				" where bc.wms_storage_batch_id = mx.wms_storage_batch_id " ;
		if(colorID != null && !colorID.equals("") && !colorID.equals("null")){
			sql +=	" and bc.emes_color_id = '"+colorID+"' " ;
		}
		if(sizeID != null && !sizeID.equals("") && !sizeID.equals("null")){
			sql += " and bc.emes_size_id = '"+sizeID+"' " ;
		}
		if(wlID != null && !wlID.equals("") && !wlID.equals("null")){
			sql += " and bc.m_product_id = '"+wlID+"'   " ;
		}

		sql +=" GROUP BY bc.wms_storage_batch_id   ";

		return dao.query(sql);
	}

	//查询当前物料的所属分类
	public List<Map<String,Object>> getProductType(String wlID) {
		String sql  ="SELECT  " +
				"  fl.value  as FLVALUE , wl.value as WLCODE " +
				"FROM  " +
				"  m_product wl,  " +
				"  m_product_category fl   " +
				"WHERE  " +
				"  wl.m_product_category_id = fl.m_product_category_id  " +
				"  and wl.m_product_id = ? ";

		return dao.query(sql, wlID );
	}

	//根据尺码ID查询尺码CODE
	public List<Map<String,Object>> getProductSize(String sizeId) {
		String sql  =" select code as CMCODE from emes_size where emes_size_id = ? ";

		return dao.query(sql, sizeId );
	}

	//查询CREATEBY和UPDATEBY的ID值
	public List<Map<String,Object>> getEquipmentputCreateby(String userIdtime){
		return dao.query("select CREATEDBY as CREATEDBY, UPDATEDBY as UPDATEDBY from ad_user where ad_user_id = ?",userIdtime);
	}


	//保存领料信息
	@SuppressWarnings("unchecked")
	public boolean saveStorageLists(String BATCHID, String HOUSEID, double  QTYS, String UOMID, String types, String CODE, String INTESTID, String status, String adorgId, String adclientId, String createBy, String updatedBy, Date time) {
		boolean result = false;
		String uuid = StringUtil.getUUID();
		String lastRest = "Y";
		String sql =  "INSERT INTO Wms_Storage_In_Out (ISACTIVE,Wms_Storage_In_Out_ID,Wms_Storage_Batch_ID, M_Warehouse_ID,Qty,C_Uom_ID,Opt_Mode,Opt_Code,Data_Id,STATUS,AD_ORG_ID,AD_CLIENT_ID,CREATED,CREATEDBY,UPDATED,UPDATEDBY) " +
				"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		result = dao.executeSql(sql, lastRest,uuid,BATCHID,HOUSEID,QTYS,UOMID,types,CODE,INTESTID,status,adorgId,adclientId,time,createBy,time,updatedBy) > 0;

		return result;
	}


	//领料单明细修改状态
	@SuppressWarnings("unchecked")
	public boolean updateStorageMx(String id) {
		boolean result = false;

		String Status= "OK";

		String sql =  "update  wms_material_list_mx set use_status = ? where wms_material_list_mx_id = ? ";

		result = dao.executeSql(sql,Status,id) > 0;
		return result;
	}

	//领料单明细修改数量
	@SuppressWarnings("unchecked")
	public boolean updateStorageMxList(String id, int qty) {
		boolean result = false;

		String sql =  "update  wms_material_list_mx set qty_binding = ?  where wms_material_list_mx_id = ? ";

		result = dao.executeSql(sql,qty,id) > 0;
		return result;
	}

	//领料单明细修改数量同时修改状态
	@SuppressWarnings("unchecked")
	public boolean updateStorageMxListStatus(String id, int qty) {
		boolean result = false;

		String sql =  "update  wms_material_list_mx set qty_binding = ?  , binding_status = 'OK' where wms_material_list_mx_id = ? ";

		result = dao.executeSql(sql,qty,id) > 0;
		return result;
	}

	/**
	 * 往batch_item表中插入信息
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public boolean inStorageItem(String uuid , String type, String code , User user, String foottype) {
		boolean result = false;
		String uuids = StringUtil.getUUID();

		String sql = "insert into Wms_Storage_Batch_Item "
				+ " (Wms_Storage_Batch_Item_ID,Wms_Storage_Batch_ID,Type,Code, "
				+ " AD_CLIENT_ID, AD_ORG_ID,ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY,foot_type)" +
				" values(?,?,?,?,?,?,?,now(),?,now(),?,?)";


		result =  dao.executeSql(sql, uuids,uuid,type,code, user.getClientId(), user.getOrgId(), "Y", user.getId(), user.getId(),foottype) > 0;

		return result;
	}

}
